-- @author shaodongyang
-- dws 58主站详情日志轻聚合

use hdp_lbg_supin_zplisting;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool.jar;
create temporary function StandardStringUDF AS 'com.bj58.adsp.dp.hive.offline_udf.StandardStringUDF';
create temporary function StandardIntUDF AS 'com.bj58.adsp.dp.hive.offline_udf.StandardIntUDF';
create temporary function StandardJsonUDF AS 'com.bj58.adsp.dp.hive.offline_udf.StandardJsonUDF';
create temporary function StandardArraySplitUDF AS 'com.bj58.adsp.dp.hive.offline_udf.StandardArraySplitUDF';
create temporary function ArraySubUDF AS 'com.bj58.adsp.dp.hive.offline_udf.ArraySubUDF';

insert overwrite table dws_zp_wb_n_flow_action partition (dt = '${dateSuffix}', action_type = 'infodetail', logsource = 'infodetail_zhuzhan')
select
    url,
    refer_url,
    source1,
    source2,
    source3,
    pid,
    sid,
    disp_local1,
    disp_local2,
    disp_local3,
    client_local1,
    client_local2,
    client_local3,
    disp_cate1,
    disp_cate2,
    disp_cate3,
    userid,
    ukey,
    cookieid,
    imei,
    guestuuid,
    ip,
    xforward,
    useragent,
    spider_tag,
    os,
    '-' os_version,
    app_version,
    stime,
    platform,
    div_id,
    slot,
    longitude,
    latitude,
    '{}' abtest_string,
    '{}' cuserportrait,
    '{}' entrance_param,
    '{}' tag_param,
    'info' card_type,
    1 hasinfo,
    1 typehasinfo,
    '-' keyword,
    '{}' filter_item,
    info_type requesttype,
    info_id,
    info_type,
    StandardArraySplitUDF(info_types, ',') info_types,
    '-' info_lego_tid,
    '-' info_make_up,
    '-' info_block,
    '{}' info_params,
    tjfrom,
    -999999 page_no,
    -999999 page_pos,
    search_pos,
    business_line,
    map() disp_params,
    -999999 exposure_time,
    map() exposure_params,
    info_detail_cnt,
    info_detail_times,
    map() info_detail_params,
    -999999 im_open_time,
    -999999 first_send_time,
    '-' session_id,
    '-' bc_type,
    -999999 has_ack,
    -999999 first_ack_time,
    -999999 has_read,
    -999999 first_read_time,
    -999999 has_reply,
    -999999 first_reply_time,
    -999999 new_session,
    map() im_params,
    -999999 delivery_cnt,
    array() delivery_times,
    array() delivery_resumeids,
    map() delivery_params,
    -999999 tel_cnt,
    -999999 tel_conn_cnt,
    array() tel_times,
    array() tel_conn_times,
    map() tel_params,
    -999999 yx_cost,
    -999999 yx_cash,
    map() yx_params,
    -999999 jz_cost,
    -999999 jz_cash,
    map() jz_params,
    openudid,
    clientid,
    clientid_flag
FROM
    (
        select
            collect_list(url) [0] url,
            collect_list(refer_url) [0] refer_url,
            collect_list(source1) [0] source1,
            collect_list(source2) [0] source2,
            collect_list(source3) [0] source3,
            collect_list(pid) [0] pid,
            sid,
            collect_list(disp_local1) [0] disp_local1,
            collect_list(disp_local2) [0] disp_local2,
            collect_list(disp_local3) [0] disp_local3,
            collect_list(client_local1) [0] client_local1,
            collect_list(client_local2) [0] client_local2,
            collect_list(client_local3) [0] client_local3,
            collect_list(disp_cate1) [0] disp_cate1,
            collect_list(disp_cate2) [0] disp_cate2,
            collect_list(disp_cate3) [0] disp_cate3,
            collect_list(userid) [0] userid,
            collect_list(ukey) [0] ukey,
            collect_list(cookieid) [0] cookieid,
            collect_list(imei) [0] imei,
            collect_list(guestuuid) [0] guestuuid,
            collect_list(ip) [0] ip,
            collect_list(xforward) [0] xforward,
            collect_list(useragent) [0] useragent,
            collect_list(spider_tag) [0] spider_tag,
            collect_list(os) [0] os,
            collect_list(app_version) [0] app_version,
            collect_list(stime) [0] stime,
            collect_list(platform) [0] platform,
            collect_list(div_id) [0] div_id,
            slot,
            collect_list(longitude) [0] longitude,
            collect_list(latitude) [0] latitude,
            info_id,
            collect_list(info_type) [0] info_type,
            collect_list(concat_ws(',', info_types))[0] info_types,
            collect_list(tjfrom) [0] tjfrom,
            collect_list(search_pos) [0] search_pos,
            collect_list(business_line) [0] business_line,
            collect_list(openudid) [0] openudid,
            collect_list(clientid) [0] clientid,
            collect_list(clientid_flag) [0] clientid_flag,
            count(1) info_detail_cnt,
            ArraySubUDF(sort_array(collect_list(cast(stime as string))),100) info_detail_times
        FROM
            dwd_zp_wb_n_infodetail
        WHERE
            dt = '${dateSuffix}'
            AND sid <> '-'
            AND slot <> '-'
            AND info_id <> -999999
        GROUP BY
            sid,
            slot,
            info_id
        union all
        select
            url,
            refer_url,
            source1,
            source2,
            source3,
            pid,
            sid,
            disp_local1,
            disp_local2,
            disp_local3,
            client_local1,
            client_local2,
            client_local3,
            disp_cate1,
            disp_cate2,
            disp_cate3,
            userid,
            ukey,
            cookieid,
            imei,
            guestuuid,
            ip,
            xforward,
            useragent,
            spider_tag,
            os,
            app_version,
            stime,
            platform,
            div_id,
            slot,
            longitude,
            latitude,
            info_id,
            info_type,
            concat_ws(',', info_types) info_types,
            tjfrom,
            search_pos,
            business_line,
            openudid,
            clientid,
            clientid_flag,
            1 info_detail_cnt,
            array(cast(stime as string)) as info_detail_times
        from
            dwd_zp_wb_n_infodetail
        where
            dt = '${dateSuffix}'
            and (
                sid = '-'
                or slot = '-'
                or info_id = -999999
            )
    ) base;